package com.analysis.service.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import org.springframework.stereotype.Service;

import com.analysis.service.SQLliteService;

@Service
public class SQLliteServiceImpl implements SQLliteService {

    private static String database = "jdbc:sqlite:D:/stock/sqlite/analyse.db";

    @Override
    public Connection getConnection() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(database);
            System.out.println("Connection to SQLite has been established.");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    public void createNewTable(String sql) {
        try (Connection conn = getConnection(); Statement stmt = conn.createStatement()) {
            stmt.execute(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static String getSql(String symbol) {
        return "CREATE TABLE IF NOT EXISTS " + symbol + " (" + "" + "`day` date," + "`open` double,"
                + "`close` double," + "`high` double," + "`low` double" + "" + ") ";
    }

    public static void main(String[] args) {
        new SQLliteServiceImpl().createNewTable(getSql("stock"));
    }
}
